package com.newegg.datafeed.adserving.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.newegg.datafeed.adserving.domain.Task;
import com.newegg.datafeed.adserving.response.Group;

@Mapper
public interface TaskMapper {

	@Select("SELECT id,name,channel,channel_name,filename,company,company_name,username,adnum,flishnum,create_date,update_date,flish FROM Task")
	@Results({
		@Result(property = "channelName",  column = "channel_name"),
		@Result(property = "companyName",  column = "company_name"),
		@Result(property = "createDate",  column = "create_date"),
		@Result(property = "updateDate",  column = "update_date")
	})
	void getAll();
	
	@Insert("INSERT INTO Task(name,channel,channel_name,filename,company,company_name,username,adnum,flishnum,create_date,update_date,flish) VALUES (#{name},#{channel},#{channelName},#{filename},#{company},#{companyName},#{username},#{adnum},#{flishnum},#{createDate},#{updateDate},#{flish});")
	void insert(Task task);
	
	@Update("UPDATE Task SET name=#{name},channel=#{channel},channel_name=#{channelName},filename=#{filename},company=#{company},company_name=#{companyName},username=#{username},adnum=#{adnum},flishnum=#{flishnum},create_date=#{createDate},update_date=#{updateDate},flish=#{flish} WHERE id =#{id}")
	void update(Task task);

	@Select("SELECT count(id) FROM Task")
	Long count();
	
	@Select("SELECT count(id) FROM Task where username=#{username}")
	Long countByUser(@Param("username")String username);
	
	@Select("SELECT id,name,channel,channel_name,filename,company,company_name,username,adnum,flishnum,create_date,update_date,flish FROM Task "
			+ "where username=#{username} limit #{start},#{size}")
    @Results({
    	@Result(property = "channelName",  column = "channel_name"),
		@Result(property = "companyName",  column = "company_name"),
		@Result(property = "createDate",  column = "create_date"),
		@Result(property = "updateDate",  column = "update_date")
    })
	List<Task> pageByUser(@Param("username")String username, @Param("start") int start,@Param("size") Integer pageSize);

	
	@Select("SELECT count(id) FROM Task where company=#{company}")
	Long countByCompany(@Param("company")int company);
	
	@Select("SELECT id,name,channel,channel_name,filename,company,company_name,username,adnum,flishnum,create_date,update_date,flish FROM Task "
			+ "where company=#{company} limit #{start},#{size}")
    @Results({
    	@Result(property = "channelName",  column = "channel_name"),
		@Result(property = "companyName",  column = "company_name"),
		@Result(property = "createDate",  column = "create_date"),
		@Result(property = "updateDate",  column = "update_date")
    })
	List<Task> pageByCompany(@Param("company")int company, @Param("start") int start,@Param("size") Integer pageSize);

	@Select("select sum(flishnum) from Task")
	Long jobCount();
	
	@Select("SELECT sum(flishnum) FROM Task where DATE_FORMAT(create_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
	Long monthCount();
	
	@Select("SELECT sum(adnum - flishnum) FROM Task where flish=false")
	Long runningCount();
	
	@Select("select sum(flishnum) from Task WHERE username=#{username}")
	Long jobCountByUser(@Param("username")String username);
	
	@Select("SELECT sum(flishnum) FROM Task where username=#{username} AND DATE_FORMAT(create_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
	Long monthCountByUser(@Param("username")String username);
	
	@Select("SELECT sum(adnum - flishnum) FROM Task where flish=false AND username=#{username}")
	Long runningCountByUser(@Param("username")String username);
	
	@Select("select sum(flishnum) from Task WHERE company=#{company}")
	Long jobCountByCompany(@Param("company")int company);
	
	@Select("SELECT sum(flishnum) FROM Task where company=#{company} AND DATE_FORMAT(create_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
	Long monthCountByCompany(@Param("company")int company);
	
	@Select("SELECT sum(adnum - flishnum) FROM Task where flish=false AND company=#{company}")
	Long runningCountByCompany(@Param("company")int company);
	
	@Select("select date_format(create_date, '%Y-%m') as name, sum(adnum) as value from Task WHERE date_format(create_date, '%Y') = date_format(now(), '%Y') group by date_format(create_date, '%Y-%m');")
	List<Group> groupMonth();
	
	@Select("select date_format(create_date, '%Y-%m') as name, sum(adnum) as value from Task WHERE date_format(create_date, '%Y') = date_format(now(), '%Y') AND company=#{company} group by date_format(create_date, '%Y-%m');")
	List<Group> groupMonthByCompany(@Param("company")int company);
	
	@Select("select date_format(create_date, '%Y-%m') as name, sum(adnum) as value from Task WHERE date_format(create_date, '%Y') = date_format(now(), '%Y') AND username=#{username} group by date_format(create_date, '%Y-%m');")
	List<Group> groupMonthByUser(@Param("username")String username);
	
	@Select("select channel as name, sum(adnum) as value from Task group by channel;")
	List<Group> groupChannel();
	
	@Select("select channel as name, sum(adnum) as value from Task where company=#{company} group by channel;")
	List<Group> groupChannelByCompany(@Param("company")int company);
	
	@Select("select channel as name, sum(adnum) as value from Task where username=#{username} group by channel;")
	List<Group> groupChannelByUser(@Param("username")String username);
}
